SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


alter   view v_tb_purrequest 
as
select cast(0 as bit) choice,m.bcode m_bcode,m.bdate m_bdate,m.remark m_remark,m.bkind m_bkind,m.bstate m_bstate,
  m.custom1 m_custom1,m.custom2 m_custom2,m.custom3 m_custom3,m.custom4 m_custom4,m.custom5 m_custom5, 
  m.custom6 m_custom6,m.custom7 m_custom7,m.custom8 m_custom8,m.custom9 m_custom9,m.custom10 m_custom10, 
  m.custom11 m_custom11,m.custom12 m_custom12,m.custom13 m_custom13,m.custom14 m_custom14,m.custom15 m_custom15, 
  m.create_by m_create_by,m.filid m_filid,m.check_by m_check_by,120 as btype,m.deptid m_deptid,m.bid m_bid,
  m.pause m_pause,
  m.projectid m_projectid,pj.code m_pjcode,pj.name m_pjname,
  cr.ui_username m_createuser, 
  ck.ui_username m_checkuser,
  f.fi_filnm m_filname,
  dt.ed_deptnm m_deptnm,
  '' as producttypename, p.producttypeid,  p.code p_code,p.[name] p_name,p.spec p_spec,p.[name] productname,
  p.custom1 p_custom1, p.custom2 p_custom2, p.custom3 p_custom3, p.custom4 p_custom4, p.custom5 p_custom5 ,
  p.custom6 p_custom6, p.custom7 p_custom7, p.custom8 p_custom8, p.custom9 p_custom9, p.custom10 p_custom10, 
  p.custom11 p_custom11, p.custom12 p_custom12, p.custom13 p_custom13, p.custom14 p_custom14, p.custom15 p_custom15,
  dp.code depotcode,dp.[name] depotname,
  u.unitname, bu.unitname baseunitname, au.unitname unitassname, u.barcode,
  d.*,
  d.relationqty*dbo.f_getunitrate(d.productid,d.unitid) as drelationqty, 
  d.grelationqty*dbo.f_getunitrate(d.productid,d.unitid) as dgrelationqty, (case when (d.pause = 1) then 
      (d.bquantity-d.relationqty*dbo.f_getunitrate(d.productid,d.unitid)) else 0 end) as pauseqty,
      cast(substring(cast(d.balancedate as varchar(20)),1,10) as datetime) as dbalancedate
  from tb_purrequest m join tb_purrequestdtl d on m.bid=d.bid
 -- left join td_trader t on m.traderid=t.traderid
  left join ts_usersinfo cr on m.create_by=cr.ui_userid
  left join ts_usersinfo ck on m.check_by=ck.ui_userid
  left join td_filiale f on m.filid=f.fi_filid
  left join td_product p on d.productid=p.p_id
  left join td_depot dp on d.depotid=dp.depotid
  left join td_unitprice u on d.unitid=u.unitid
  left join td_unitprice bu on d.productid=bu.productid and bu.baseunit=1
  left join td_unitprice au on d.productid=au.productid and au.u_no=0
  left join td_dept dt on dt.ed_deptid = m.deptid
  left join tfd_project pj on m.projectid=pj.id  






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

